package northwind.repository.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import northwind.model.Category;
import northwind.model.Product;
import northwind.repository.ProductRepository;
import northwind.util.EntityUtils;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

@Repository
public class JdbcProductRepositoryImpl extends AbstractNorthwindJdbcRepository
        implements ProductRepository {

    //
    private final static String ALL_CATEGORY_SQL = "SELECT CategoryID, CategoryName, Description FROM CATEGORIES";
    private final static String FIND_CATEGORY_BY_ID_SQL = "SELECT CategoryID, CategoryName, Description FROM CATEGORIES WHERE CategoryID=?";
    private final static String CREATE_CATEGORY_SQL = "INSERT INTO CATEGORIES( CategoryName, Description) VALUES (?,?)";
    private final static String UPDATE_CATEGORY_SQL = "UPDATE CATEGORIES SET CategoryName=?, Description=? WHERE CategoryID= ?";
    private final static String DELETE_CATEGORY_SQL = "DELETE FROM CATEGORIES WHERE CategoryID= ?";

    //
    private static final String PRODUCT_PROPERTIES = " ProductID ,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued ";
    private static final String ALL_PRODUCT_SQL = "SELECT "
            + PRODUCT_PROPERTIES + "  FROM Products";
    private static final String ALL_PRODUCT_BY_CATEGORY_SQL = "SELECT "
            + PRODUCT_PROPERTIES + " FROM Products WHERE CategoryID=? ";

    private class JdbcProduct extends Product {

        private int categoryId;

        public int getCategoryId() {
            return categoryId;
        }

        public void setCategoryId(int categoryId) {
            this.categoryId = categoryId;
        }

    }

    private class ProductRowMapper implements RowMapper<JdbcProduct> {

        @Override
        public JdbcProduct mapRow(ResultSet rs, int arg1) throws SQLException {
            JdbcProduct product = new JdbcProduct();
            product.setProductID(rs.getInt("ProductID"));
            product.setProductName(rs.getString("ProductName"));
            product.setCategoryId(rs.getInt("CategoryID"));
            return product;
        }

    }

    @Autowired
    public JdbcProductRepositoryImpl(JdbcTemplate jdbcTemplate) {
        super(jdbcTemplate);
    }

    @Override
    public Collection<Category> findAllCategory() throws DataAccessException {
        List<Category> categories = new ArrayList<Category>();
        List<Category> allCategories = this.jdbcTemplate.query(
                ALL_CATEGORY_SQL,
                ParameterizedBeanPropertyRowMapper.newInstance(Category.class));

        categories.addAll(allCategories);

        return categories;
    }

    @Override
    public Category findCategoryById(int id) throws DataAccessException {

        List<Category> results = jdbcTemplate.query(FIND_CATEGORY_BY_ID_SQL,
                new Object[]{id},
                ParameterizedBeanPropertyRowMapper.newInstance(Category.class));

        return results.size() == 1 ? results.get(0) : null;
    }

    @Override
    public int createCategory(String name, String description)
            throws DataAccessException {
        int createResult = this.jdbcTemplate.update(CREATE_CATEGORY_SQL, name,
                description);
        return createResult;
    }

    @Override
    public int updateCategory(int id, String newName, String newDescription)
            throws DataAccessException {

        int updateResult = this.jdbcTemplate.update(UPDATE_CATEGORY_SQL,
                newName, newDescription, id);
        return updateResult;
    }

    @Override
    public int deleteCategory(int id) throws DataAccessException {
        int deleteResult = this.jdbcTemplate.update(DELETE_CATEGORY_SQL, id);
        return deleteResult;
    }

    @Override
    public Collection<Product> findAllProduct() throws DataAccessException {

        List<Product> products = new ArrayList<Product>();
        List<JdbcProduct> queryProducts = this.jdbcTemplate.query(
                ALL_PRODUCT_SQL, new ProductRowMapper());

        products.addAll(queryProducts);

        for (JdbcProduct product : queryProducts) {
            product.setCategory(EntityUtils.getById(findAllCategory(),
                    Category.class, product.getCategoryId()));
        }

        return products;
    }

    @Override
    public Collection<Product> findAllProductByCategory(int categoryID)
            throws DataAccessException {
        List<Product> products = new ArrayList<Product>();
        List<JdbcProduct> queryProducts = this.jdbcTemplate.query(
                ALL_PRODUCT_BY_CATEGORY_SQL, new Object[]{categoryID},
                new ProductRowMapper());
        products.addAll(queryProducts);

        for (JdbcProduct product : queryProducts) {
            product.setCategory(EntityUtils.getById(findAllCategory(),
                    Category.class, product.getCategoryId()));
        }

        return products;
    }

}
